package cn.fldong.dao.impl;

import cn.fldong.dao.CourseDAO;
import cn.fldong.model.Course;
import cn.fldong.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;

/**
 * CourseDAO接口的实现类，提供课程相关的数据库操作实现
 */
public class CourseDAOImpl implements CourseDAO {
    private static final String GET_COURSE_BY_ID = "SELECT * FROM courses WHERE course_id = ?";
    private static final String GET_COURSES_BY_NAME = "SELECT * FROM courses WHERE course_name LIKE ? ORDER BY course_id";
    private static final String GET_COURSES_BY_SEMESTER = "SELECT * FROM courses WHERE semester = ? ORDER BY course_id";
    private static final String GET_ALL_COURSES = "SELECT * FROM courses ORDER BY course_id";
    private static final String ADD_COURSE = "INSERT INTO courses(course_id, course_name, course_code, credit, hours, semester, teacher, description) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_COURSE = "UPDATE courses SET course_name=?, course_code=?, credit=?, hours=?, semester=?, teacher=?, description=? WHERE course_id=?";
    private static final String DELETE_COURSE = "DELETE FROM courses WHERE course_id = ?";

    /**
     * 将ResultSet映射为Course对象的私有辅助方法
     */
    private Course mapResultSetToCourse(ResultSet rs) throws SQLException {
        Course course = new Course();
        course.setCourseId(rs.getString("course_id"));
        course.setCourseName(rs.getString("course_name"));
        course.setCourseCode(rs.getString("course_code"));
        course.setCredit(rs.getBigDecimal("credit"));
        course.setHours(rs.getInt("hours"));
        course.setSemester(rs.getString("semester"));
        course.setTeacher(rs.getString("teacher"));
        course.setDescription(rs.getString("description"));
        course.setCreatedAt(rs.getTimestamp("created_at"));
        course.setUpdatedAt(rs.getTimestamp("updated_at"));
        return course;
    }

    @Override
    public Course getCourseById(String courseId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Course course = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_COURSE_BY_ID);
            stmt.setString(1, courseId);
            rs = stmt.executeQuery();

            if (rs.next()) {
                course = mapResultSetToCourse(rs);
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return course;
    }
    
    @Override
    public boolean courseNameExists(String courseName) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM courses WHERE course_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, courseName);
            rs = stmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
            return false;
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }
    }

    @Override
    public List<Course> getCoursesByName(String courseName) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Course> courseList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_COURSES_BY_NAME);
            stmt.setString(1, "%" + courseName + "%"); // 模糊查询
            rs = stmt.executeQuery();

            while (rs.next()) {
                courseList.add(mapResultSetToCourse(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return courseList;
    }

    @Override
    public List<Course> getCoursesBySemester(String semester) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Course> courseList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_COURSES_BY_SEMESTER);
            stmt.setString(1, semester);
            rs = stmt.executeQuery();

            while (rs.next()) {
                courseList.add(mapResultSetToCourse(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return courseList;
    }

    @Override
    public List<Course> getAllCourses() throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Course> courseList = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(GET_ALL_COURSES);
            rs = stmt.executeQuery();

            while (rs.next()) {
                courseList.add(mapResultSetToCourse(rs));
            }
        } finally {
            DBUtil.closeResources(conn, stmt, rs);
        }

        return courseList;
    }

    @Override
    public boolean addCourse(Course course) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(ADD_COURSE);
            stmt.setString(1, course.getCourseId());
            stmt.setString(2, course.getCourseName());
            stmt.setString(3, course.getCourseCode());
            stmt.setBigDecimal(4, course.getCredit());
            stmt.setInt(5, course.getHours());
            stmt.setString(6, course.getSemester());
            stmt.setString(7, course.getTeacher());
            stmt.setString(8, course.getDescription());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean updateCourse(Course course) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(UPDATE_COURSE);
            stmt.setString(1, course.getCourseName());
            stmt.setString(2, course.getCourseCode());
            stmt.setBigDecimal(3, course.getCredit());
            stmt.setInt(4, course.getHours());
            stmt.setString(5, course.getSemester());
            stmt.setString(6, course.getTeacher());
            stmt.setString(7, course.getDescription());
            stmt.setString(8, course.getCourseId());

            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }

    @Override
    public boolean deleteCourse(String courseId) throws SQLException {
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.prepareStatement(DELETE_COURSE);
            stmt.setString(1, courseId);
            return stmt.executeUpdate() > 0;
        } finally {
            DBUtil.closeResources(conn, stmt);
        }
    }
}